<?php
namespace app\admin\controller;

use app\common\model\Channel;
use think\Controller;
use app\common\model\CpChannel;
use app\common\model\Order as GamePayLogModel;
use app\common\model\SettlementGame;
use app\common\model\Stat as Stats;
use PHPExcel;
use PHPExcel_IOFactory;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Border;
use PHPExcel_Style_Fill;

use think\Db;
use think\facade\Session;

class ExportExcel extends Controller
{
    public function exportExcel(){
        $data = $_REQUEST;
        $type = $data["type"];
        ini_set('memory_limit', '512M');
        set_time_limit(0);
        switch ($type){
            case 'user':
                $result = self::export_user($data);
                $expTitle = $result['title'];
                $expCellName = $result['cells'];
                $expTableData = $result['tableData'];
                break;
            case 'order':
                $result = self::export_order($data);
                $expTitle = $result['title'];
                $expCellName = $result['cells'];
                $expTableData = $result['tableData'];
                break;
            case 'stat':
                $result = self::export_stat($data);
                $expTitle = $result['title'];
                $expCellName = $result['cells'];
                $expTableData = $result['tableData'];
                break;
            case 'days':
                $result = self::export_days($data);
                $expTitle = $result['title'];
                $expCellName = $result['cells'];
                $expTableData = $result['tableData'];
                break;
            case 'months':
                $result = self::export_months($data);
                $expTitle = $result['title'];
                $expCellName = $result['cells'];
                $expTableData = $result['tableData'];
                break;
            default:
                $this->error("导出类型错误！");
        }
        self::export($expTitle,$expCellName,$expTableData);
    }

    private static function export_order($data)
    {
        $expTitle = "订单列表";
        $expCellName = array(
            array('order_no','订单号'),
            array('account','玩家帐号'),
            array('game_name','注册游戏'),
            array('channel','所属渠道'),
            array('amount','充值金额'),
            array('type','支付方式'),
            array('pay_time','充值时间'),
            array('order_status','支付状态'),
        );
        $order_status = [
            '0'=>'未支付',
            '1'=>'已支付',
            '2'=>'已充值',
            '3'=>'已发货',
            '4'=>'通知游戏失败',
        ];

        $pay_type = [
            '1' =>'支付宝',
            '2' =>'微信',
        ];

        /* 查询条件初始化 */
        $data = input();
        $map  = [];
        if(!empty($data['keyword'])){
            $map[]  = ['a.uid|a.order_no','like', '%'.$data['keyword'].'%'];
        }
        $join = 'Player';
        if(!empty($data['account'])){
            $map[]  = ['c.account','like', '%'.$data['account'].'%'];
        }
        if(!empty($data['game'])){
            $map[]   = ['a.gid','=' ,$data['game']];
        }else{
            $data['game']   = null;
        }
        if(isset($data['cid']) && $data['cid']!=""){
            $map[]  = ['a.cid','=',$data['cid']];
        }


        if(!empty($data['status'])){
            switch ($data['status']){
                case 1:
                    $map[]  = ['a.order_status','=',$data['status']-1];
                    break;
                case 2:
                    $map[]  = ['a.order_status','>',0];
                    break;
                case 3:
                    $map[]  = ['a.order_status','=',$data['status']];
                    break;
                case 4:
                    $map[]  = ['a.order_status','=',$data['status']];
                    break;
            }

        }else{
            $data['status']    = null;
        }

        if (!empty($data['starttime']) && !empty($data['endtime'])) {
            $map[]= ['a.pay_time','between time',[$data['starttime'],$data['endtime']]];
        }elseif (!empty($data['starttime'])) {
            $map[] = ['a.pay_time','>=',$data['starttime']];
        }elseif (!empty($data['endtime'])) {
            $map[] = ['a.pay_time','<=',$data['endtime']];
        }

        $field = 'a.*,b.name,c.account,d.username as aname,d.nickname';
        $orderList = GamePayLogModel::alias('a')
            ->join('Gamelist b','b.id = a.gid','left')
            ->join('Player c','c.id = a.uid','left')
            ->join('Channel d','d.id = a.cid','left')
            ->where($map)->field($field)
            ->order('a.id DESC')
            ->select();
        $expTableData = [];
        foreach ($orderList as &$key){
            $order['order_no'] = $key['order_no'];
            $order['account'] = $key['gid'] == 30 ? get_old_player($key['uid']):$key['account'];
            $order['game_name'] = $key['name'];
            $order['channel'] = $key['nickname'];
            $order['amount'] = $key['amount'];
            $order['type'] = $pay_type[$key['type']];
            $order['pay_time'] = $key['pay_time'];
            $order['order_status'] = $order_status[$key['order_status']];
            $expTableData[] = $order;
        }
        return ['title'=>$expTitle,'cells'=>$expCellName,'tableData'=>$expTableData];
    }
    private static function export_user($data){
        $age_status = [
            '0' => '未认证',
            '1' => '未成年',
            '2' => '已成年',
        ];
        $expTitle = "玩家列表";
        $expCellName = array(
            array('id','玩家ID'),
            array('account','玩家帐号'),
            array('game_name','注册游戏'),
            array('channel','所属渠道'),
            array('login_time','最后登录时间'),
            array('login_ip','登录IP'),
            array('status','账号状态'),
            array('type','注册方式'),
            array('is_age','是否实名认证'),
        );
        $map = [];
        if(!empty($data['ip'])){
            $map[]  = ['login_ip','=',$data['ip']];
        }
        if(!empty($data['game'])){
            $map[]   = ['gid','=' ,$data['game']];
        }
        if (!empty($data['starttime']) && !empty($data['endtime'])) {
            $map[]= ['between time',[strtotime($data['starttime']),strtotime($data['endtime'])]];
        }elseif (!empty($data['starttime'])) {
            $map[] = ['login_time','>=',strtotime($data['starttime'])];
        }elseif (!empty($data['endtime'])) {
            $map[] = ['login_time','<=',strtotime($data['endtime'])];
        }
        $expTableData = \app\common\model\Player::field('id,account,gid,cid,login_time,login_ip,status,type,is_age')->where($map)->order("id desc")->select()->toArray();
        foreach ($expTableData as &$key){
            $key['account'] = "'".$key['account'];
            $key['game_name'] = get_game_filed($key['gid'],'name');
            $key['channel'] = get_channel_name($key['cid'],'nickname');
            $key['login_time'] = date("Y-m-d H:i:s",$key['login_time']);
            $key['status'] = $key['status'] == 1 ? '正常' : '禁用';
            $key['type'] = $key['type'] == 1 ? '手机号注册' : '账号注册';
            $key['is_age'] = $age_status[$key['is_age']];
            unset($key['gid']);
            unset($key['cid']);
        }
        return ['title'=>$expTitle,'cells'=>$expCellName,'tableData'=>$expTableData];
    }

    private static function export_stat($data){
        $expTitle = "统计列表";
        $expCellName = array(
            array('gid','游戏名称'),
            array('stat_date','日期'),
            array('new_users','新增用户'),
            array('new_roles','新增角色'),
            array('active_users','活跃人数'),
            array('pay_amount','付费金额'),
            array('pay_users','付费人数'),
            array('pay_times','付费次数'),
            array('arpu','ARPU'),
            array('arppu','ARPPU'),
        );
        $where = [];
        $stat = new Stats();
        if(isset($data['cid'])){
            $where[] = ['cid', '=', $data['cid']];
        }
        if(!empty($data['gid'])){
            $where[] = ['gid', '=', $data['gid']];
        }
        if (!empty($data['starttime']) && !empty($data['endtime'])) {
            $where[]= ['stat_date','between time',[$data['starttime'],$data['endtime']]];
        }elseif (!empty($data['starttime'])) {
            $where[] = ['stat_date','>=',$data['starttime']];
        }elseif (!empty($data['endtime'])) {
            $where[] = ['stat_date','<=',$data['endtime']];
        }
        $list = $stat->where($where)
            ->order("id desc")
            ->select()->toArray();
        $expTableData = [];
        foreach ($list as $val){
            $expTableData[] = array(
                'gid' => get_game_filed($val['gid'],'name'),
                'stat_date' => $val['stat_date'],
                'new_users' => $val['new_users'],
                'new_roles' => $val['new_roles'],
                'active_users' => $val['active_users'],
                'pay_amount' => $val['pay_amount'],
                'pay_users' => $val['pay_users'],
                'pay_times' => $val['pay_times'],
                'arpu' => $val['arpu'],
                'arppu' => $val['arppu'],
            );
        }
        return ['title'=>$expTitle,'cells'=>$expCellName,'tableData'=>$expTableData];
    }


    private static function export_days($data)
    {
        $expTitle = "每日数据统计";
        $expCellName = array(
            array('stat_date','日期'),
            array('new_users','新增用户'),
            array('new_roles','新增角色'),
            array('active_users','活跃人数'),
            array('pay_amount','付费金额'),
            array('pay_users','付费人数'),
            array('pay_times','付费次数'),
            array('arpu','ARPU'),
            array('arppu','ARPPU'),
        );
        $where = [];
        $stat = new Stats();
        if(!empty($data['gid'])){
            $where[] = ['gid', '=', $data['gid']];
        }
        if(isset($data['cid'])){
            $where[] = ['cid', '=', $data['cid']];
        }
        if (!empty($data['starttime']) && !empty($data['endtime'])) {
            $where[]= ['stat_date','between time',[$data['starttime'],$data['endtime']]];
        }elseif (!empty($data['starttime'])) {
            $where[] = ['stat_date','>=',$data['starttime']];
        }elseif (!empty($data['endtime'])) {
            $where[] = ['stat_date','<=',$data['endtime']];
        }

        $list = $stat->field('stat_date, sum(new_users) as new_users, sum(new_roles) as new_roles,
            sum(active_users) as active_users, sum(pay_amount) as pay_amount, sum(pay_users) as pay_users, sum(pay_times) as pay_times')
            ->where($where)
            ->order("id desc")
            ->group('stat_date')
            ->select()->toArray();
        $expTableData = [];
        foreach ($list as $val){
            $activeUsers = $val['active_users'] ?? 0;
            $payAmount = $val['pay_amount'] ?? 0;
            $payUsers = $val['pay_users'] ?? 0;

            $arpu = $activeUsers > 0 ? round($payAmount / $activeUsers, 2) : 0;
            $arppu = $payUsers > 0 ? round($payAmount / $payUsers, 2) : 0;

            $expTableData[] = array(
                'stat_date' => $val['stat_date'],
                'new_users' => $val['new_users'],
                'new_roles' => $val['new_roles'],
                'active_users' => $val['active_users'],
                'pay_amount' => $val['pay_amount'],
                'pay_users' => $val['pay_users'],
                'pay_times' => $val['pay_times'],
                'arpu' => $arpu,
                'arppu' => $arppu,
            );
        }
        return ['title'=>$expTitle,'cells'=>$expCellName,'tableData'=>$expTableData];
    }

    public function export_settle(){
        $params = $this->request->param();
        if($params['type'] == 2){
            $this->export_channel_settle($params);exit;
        }
        $where = [];
        if (!empty($params['date_month'])) {
            $params['date_month'] = date("Y-m",$params['date_month']);
            $where[]= ['date_month','=',$params['date_month']];
        }
        if (!empty($params['cp_id'])) {
            $where[]= ['cp_id','=',$params['cp_id']];
        }
        $settle = new SettlementGame();
        $list = $settle
            ->where($where)
            ->order("id desc")
            ->select();

        if($params['type'] == 2){
            // 渠道信息
            $cpData = Channel::find($params['cp_id']);
        }else{

        }
        // cp信息
        $cpData = CpChannel::find($params['cp_id']);

        // 创建PHPExcel对象
        $objPHPExcel = new PHPExcel();

        // 设置当前活动sheet
        $objPHPExcel->setActiveSheetIndex(0);
        $sheet = $objPHPExcel->getActiveSheet();

        // 设置sheet标题
        $sheet->setTitle('分成结算报表');

        // 合并单元格并设置标题
        $sheet->mergeCells('A1:H1');
        $sheet->setCellValue('A1', '分成结算报表');
        $sheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(16);

        // 设置表头
        $headers = ['分成项目', '合作方', '结算周期', '用户累计充值', '渠道费率', '渠道费', '分成比例', '分成收入'];
        $sheet->fromArray($headers, null, 'A2');

        // 设置表头样式
        $headerStyle = [
            'font' => ['bold' => true],
            'alignment' => ['horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER],
            'fill' => [
                'type' => PHPExcel_Style_Fill::FILL_SOLID,
                'color' => ['rgb' => 'F2F2F2']
            ],
            'borders' => [
                'allborders' => [
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => ['rgb' => '000000']
                ]
            ]
        ];
        $sheet->getStyle('A2:H2')->applyFromArray($headerStyle);

        // 填充数据
        $row = 3;
        $totalAmount = 0;
        $totalFee = 0;
        $totalIncome = 0;

        foreach ($list as $item) {
            $sheet->setCellValue('A'.$row, get_game_filed($item['gid'], 'name'));
            $sheet->setCellValue('B'.$row, get_cp_name($item['cp_id']));
            $sheet->setCellValue('C'.$row, $item['date_month']);
            $sheet->setCellValue('D'.$row, $item['amount']);
            $sheet->setCellValue('E'.$row, get_game_filed($item['gid'], 'cfencheng').'%');
            $sheet->setCellValue('F'.$row, round($item['g_fee'], 2));
            $sheet->setCellValue('G'.$row, get_game_filed($item['gid'], 'gfencheng').'%');
            $sheet->setCellValue('H'.$row, round($item['g_fencheng'], 2));

            $totalAmount += $item['amount'];
            $totalFee += $item['g_fee'];
            $totalIncome += $item['g_fencheng'];

            $row++;
        }

        // 设置数据区域统一列宽
        $sheet->getDefaultColumnDimension()->setWidth(15); // 设置默认列宽为15
        // 特殊列宽设置（覆盖默认值）
        $sheet->getColumnDimension('A')->setWidth(20); // 分成项目列宽20
        $sheet->getColumnDimension('H')->setWidth(15); // 分成收入列宽15

        // 设置数据区域边框（包含所有数据行）
        $dataStyle = [
            'alignment' => [
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER
            ],
            'borders' => [
                'allborders' => [
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => ['rgb' => '000000']
                ]
            ]
        ];
        $sheet->getStyle('A2:H'.($row-1))->applyFromArray($dataStyle);

        // 添加合计行
        $sheet->setCellValue('A'.$row, '合计')->getStyle('A'.$row)->getFont()->setBold(true);
        $sheet->setCellValue('D'.$row, $totalAmount)->getStyle('D'.$row)->getFont()->setBold(true);
        $sheet->setCellValue('F'.$row, round($totalFee, 2))->getStyle('F'.$row)->getFont()->setBold(true);
        $sheet->setCellValue('H'.$row, round($totalIncome, 2))->getStyle('H'.$row)->getFont()->setBold(true);
        $sheet->getStyle('A'.$row.':H'.$row)->applyFromArray($dataStyle);

        $row++;

        // 添加付款方和收款方信息
        $sheet->mergeCells('A'.$row.':D'.$row);
        $sheet->mergeCells('E'.$row.':H'.$row);
        $sheet->setCellValue('A'.$row, '付款方：')->getStyle('A'.$row)->getFont()->setBold(true);
        $sheet->setCellValue('E'.$row, '收款方：')->getStyle('E'.$row)->getFont()->setBold(true);
        $sheet->getStyle('A'.$row.':H'.$row)->applyFromArray([
            'borders' => [
                'allborders' => [
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => ['rgb' => '000000']
                ]
            ]
        ]);

        $row++;

        // 付款方详细信息
        $paymentInfo = "发票抬头：艾七（上海）科技有限公司\n"
            ."发票内容:信息系统服务*信息服务费\n"
            ."纳税识别号：91310120MA7F0F8C3L\n"
            ."银行账号：31050161364000007664\n"
            ."开户银行：中国建设银行股份有限公司上海浦东分行\n"
            ."注册电话：18811546788\n"
            ."注册地址：上海市闵行区虹梅南路1755号1幢1层\n"
            ."发票类型: 6%增值税专用发票";

        // 收款方详细信息
        $receiverInfo = "开户名称：".$cpData['company']."\n"
            ."开户银行：".$cpData['bank']."\n"
            ."银行账号：".$cpData['bank_num']."\n"
            ."联系人：".$cpData['contacts']."\n"
            ."联系电话：".$cpData['phone']."\n"
            ."收件地址：".$cpData['address'];

        // 设置付款方和收款方信息区域（带边框）
        $sheet->mergeCells('A'.$row.':D'.($row+7));
        $sheet->mergeCells('E'.$row.':H'.($row+7));
        $sheet->setCellValue('A'.$row, $paymentInfo);
        $sheet->setCellValue('E'.$row, $receiverInfo);

        // 设置信息单元格样式（带边框）
        $infoStyle = [
            'alignment' => [
                'wrap' => true,
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP
            ],
            'borders' => [
                'allborders' => [
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => ['rgb' => '000000']
                ]
            ]
        ];
        $sheet->getStyle('A'.$row.':H'.($row+6))->applyFromArray($infoStyle);

        // 设置行高（自动调整）
        for ($i = $row; $i <= $row+6; $i++) {
            $sheet->getRowDimension($i)->setRowHeight(-1); // -1表示自动调整行高
        }

        // 输出Excel文件
        $filename = $cpData['company'].'-分成结算报表_'.$params['date_month'].'.xlsx';

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');
        exit;
    }

    private function export_channel_settle($params)
    {
        $where = [];
        if (!empty($params['date_month'])) {
            $params['date_month'] = date("Y-m",$params['date_month']);
            $where[]= ['date_month','=',$params['date_month']];
        }
        if (!empty($params['cp_id'])) {
            $where[]= ['cid','=',$params['cp_id']];
        }
        $settle = new SettlementGame();
        $list = $settle
            ->where($where)
            ->order("id desc")
            ->select();

        // 渠道信息
        $cpData = Channel::find($params['cp_id']);

        // 创建PHPExcel对象
        $objPHPExcel = new PHPExcel();

        // 设置当前活动sheet
        $objPHPExcel->setActiveSheetIndex(0);
        $sheet = $objPHPExcel->getActiveSheet();

        // 设置sheet标题
        $sheet->setTitle('分成结算报表');

        // 合并单元格并设置标题
        $sheet->mergeCells('A1:F1');
        $sheet->setCellValue('A1', '分成结算报表');
        $sheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(16);

        // 设置表头
        $headers = ['分成项目', '合作方', '结算周期', '用户累计充值', '分成比例', '分成收入'];
        $sheet->fromArray($headers, null, 'A2');

        // 设置表头样式
        $headerStyle = [
            'font' => ['bold' => true],
            'alignment' => ['horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER],
            'fill' => [
                'type' => PHPExcel_Style_Fill::FILL_SOLID,
                'color' => ['rgb' => 'F2F2F2']
            ],
            'borders' => [
                'allborders' => [
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => ['rgb' => '000000']
                ]
            ]
        ];
        $sheet->getStyle('A2:F2')->applyFromArray($headerStyle);

        // 填充数据
        $row = 3;
        $totalAmount = 0;
        $totalFee = 0;
        $totalIncome = 0;
        $totalCIncome = 0;

        foreach ($list as $item) {
            $sheet->setCellValue('A'.$row, get_game_filed($item['gid'], 'name'));
            $sheet->setCellValue('B'.$row, get_channel_name($item['cid']));
            $sheet->setCellValue('C'.$row, $item['date_month']);
            $sheet->setCellValue('D'.$row, $item['amount']);
            $sheet->setCellValue('E'.$row, $cpData['fee'].'%');
            $sheet->setCellValue('F'.$row, round($item['c_fencheng'], 2));

            $totalAmount += $item['amount'];
            $totalFee += $item['g_fee'];
            $totalIncome += $item['g_fencheng'];
            $totalCIncome += $item['c_fencheng'];

            $row++;
        }

        // 设置数据区域统一列宽
        $sheet->getDefaultColumnDimension()->setWidth(15); // 设置默认列宽为15
        // 特殊列宽设置（覆盖默认值）
        $sheet->getColumnDimension('A')->setWidth(20); // 分成项目列宽20
        $sheet->getColumnDimension('F')->setWidth(15); // 分成收入列宽15

        // 设置数据区域边框（包含所有数据行）
        $dataStyle = [
            'alignment' => [
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER
            ],
            'borders' => [
                'allborders' => [
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => ['rgb' => '000000']
                ]
            ]
        ];
        $sheet->getStyle('A2:F'.($row-1))->applyFromArray($dataStyle);

        // 添加合计行
        $sheet->setCellValue('A'.$row, '合计')->getStyle('A'.$row)->getFont()->setBold(true);
        $sheet->setCellValue('D'.$row, $totalAmount)->getStyle('D'.$row)->getFont()->setBold(true);
//        $sheet->setCellValue('E'.$row, round($cpData['fee'], 2))->getStyle('F'.$row)->getFont()->setBold(true);
        $sheet->setCellValue('F'.$row, round($totalCIncome, 2))->getStyle('F'.$row)->getFont()->setBold(true);
        $sheet->getStyle('A'.$row.':F'.$row)->applyFromArray($dataStyle);

        $row++;

        // 添加付款方和收款方信息
        $sheet->mergeCells('A'.$row.':C'.$row);
        $sheet->mergeCells('D'.$row.':F'.$row);
        $sheet->setCellValue('A'.$row, '付款方：')->getStyle('A'.$row)->getFont()->setBold(true);
        $sheet->setCellValue('D'.$row, '收款方：')->getStyle('D'.$row)->getFont()->setBold(true);
        $sheet->getStyle('A'.$row.':F'.$row)->applyFromArray([
            'borders' => [
                'allborders' => [
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => ['rgb' => '000000']
                ]
            ]
        ]);

        $row++;

        // 付款方详细信息
        $paymentInfo = "发票抬头：艾七（上海）科技有限公司\n"
            ."发票内容:信息系统服务*信息服务费\n"
            ."纳税识别号：91310120MA7F0F8C3L\n"
            ."银行账号：31050161364000007664\n"
            ."开户银行：中国建设银行股份有限公司上海浦东分行\n"
            ."注册电话：18811546788\n"
            ."注册地址：上海市闵行区虹梅南路1755号1幢1层\n"
            ."发票类型: 6%增值税专用发票";

        // 收款方详细信息
        $receiverInfo = "开户名称：".$cpData['nickname']."\n"
            ."开户银行：".$cpData['bank']."\n"
            ."银行账号：".$cpData['bank_num']."\n"
            ."联系人：".$cpData['nickname']."\n"
            ."联系电话：".$cpData['mobile']."\n"
            ."收件地址：".$cpData['address'];

        // 设置付款方和收款方信息区域（带边框）
        $sheet->mergeCells('A'.$row.':C'.($row+7));
        $sheet->mergeCells('D'.$row.':F'.($row+7));
        $sheet->setCellValue('A'.$row, $paymentInfo);
        $sheet->setCellValue('D'.$row, $receiverInfo);

        // 设置信息单元格样式（带边框）
        $infoStyle = [
            'alignment' => [
                'wrap' => true,
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP
            ],
            'borders' => [
                'allborders' => [
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => ['rgb' => '000000']
                ]
            ]
        ];
        $sheet->getStyle('A'.$row.':F'.($row+6))->applyFromArray($infoStyle);

        // 设置行高（自动调整）
        for ($i = $row; $i <= $row+6; $i++) {
            $sheet->getRowDimension($i)->setRowHeight(-1); // -1表示自动调整行高
        }

        // 输出Excel文件
        $filename = $cpData['nickname'].'-分成结算报表_'.$params['date_month'].'.xlsx';

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');
        exit;
    }

    private static function export_months($data){
        $expTitle = "月数据统计";
        $expCellName = array(
            array('month_date','日期'),
            array('new_users','新增用户'),
            array('new_roles','新增角色'),
            array('active_users','活跃人数'),
            array('pay_amount','付费金额'),
            array('pay_users','付费人数'),
            array('pay_times','付费次数'),
            array('arpu','ARPU'),
            array('arppu','ARPPU'),
        );
        $where = [];
        $stat = new Stats();
        if(!empty($data['gid'])){
            $where[] = ['gid', '=', $data['gid']];
        }else{
            $where[] = ['gid', '>', '0'];
        }
        if(isset($data['cid'])){
            $where[] = ['cid', '=', $data['cid']];
        }
        if (!empty($data['starttime']) && !empty($data['endtime'])) {
            $where[]= ['stat_date','between time',[$data['starttime'],$data['endtime']]];
        }elseif (!empty($data['starttime'])) {
            $where[] = ['stat_date','>=',$data['starttime']];
        }elseif (!empty($data['endtime'])) {
            $where[] = ['stat_date','<=',$data['endtime']];
        }else{
            // 默认当前月份
            $starttime = date("Y-m")."-01";
            $endtime = date("Y-m-d");
//            $endtime = date("Y-m")."-".date("t");
            $where[]= ['stat_date','between',[$starttime,$endtime]];
        }

        $expTableData = $stat->field("DATE_FORMAT(stat_date, '%Y-%m') as month_date,sum(new_users) as new_users,sum(new_roles) as new_roles,
            sum(active_users) as active_users,sum(pay_amount) as pay_amount,sum(pay_users) as pay_users,sum(pay_times) as pay_times,
            ROUND( SUM( pay_amount ) / NULLIF( SUM( new_users ), 0 ), 2 ) AS arpu,
            ROUND( SUM( pay_amount ) / NULLIF( sum( pay_users ), 0 ), 2 ) AS arppu ")
            ->where($where)
            ->group("DATE_FORMAT(stat_date, '%Y-%m')")
            ->order("month_date desc")
            ->select()->toArray();

        return ['title'=>$expTitle,'cells'=>$expCellName,'tableData'=>$expTableData];
    }

    private static function export($expTitle,$expCellName,$expTableData){
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $fileName = session('user_auth.username').date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);
        $objPHPExcel = new PHPExcel();
        $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        for($i=0;$i<$cellNum;$i++){
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i][1]);
        }
        //数据处理
        foreach ($expTableData as $key => $val) {
            foreach ($expCellName as $k => $v) {
                if (isset($v[2])) {
                    $ar_k = array_search('*', $v);
                    if ($ar_k !== false) {
                        $v[$ar_k] = $val[$v[0]];
                    }
                    $fun = $v[2];
                    $param = $v;
                    unset($param[0], $param[1], $param[2]);
                    $val[$v[0]] = call_user_func_array($fun, $param);
                }
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$k].($key+3), $val[$v[0]]);
            }
        }
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xlsx"');
        header("Content-Disposition:attachment;filename=$xlsTitle.xls");//attachment新窗口打印inline本窗口打印
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }
}
